package com.sq.partner.manager.util.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import com.sq.partner.manager.util.LogUtil;

@Component
public class ExportExcelBuilderTool {
	
	private static Logger logger = LogUtil.getLog(ExportExcelBuilderTool.class);
	
	private Workbook workbook;
	private Sheet sheet;
	private String[] titles;
	private List<Map<String,Object>> results;
	private String[] titleKeys;
	private String columns;
	private String totalTitle;
	
	public void buildExcel(HttpServletResponse response,ExcelEntity excel) throws Exception{
	    //设置response方式,使执行此controller 时候自动出现下载页面,而非直接使用excel打开
        response.setContentType( "APPLICATION/OCTET-STREAM" );
         //模板加当天时间后缀
        response.setHeader( "Content-Disposition" , "attachment; filename=" + URLEncoder.encode(excel.getFileName()+"-" +new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+ ".xls", "utf-8"));
         workbook = new HSSFWorkbook();
         titles = excel.getTitles().split(",");
         columns = excel.getColumns();
         results = excel.getResults();
         titleKeys = excel.getTitleKeys().split(",");
         totalTitle = excel.getTotalTitle();
         //输出模板
        OutputStream ouputStream = null;
         try {
              sheet = workbook.createSheet(excel.getSheetName());      //Sheet 名字
              this.buildTitle();
              this.buildContent();
              //获取输出流
             ouputStream = response.getOutputStream();
              workbook.write(ouputStream);
        } catch (IOException e) {
             logger.error(e);
        } catch (Exception e) {
             logger.error(e);
        } finally{
              try {
                   if( null != ouputStream) {
                        ouputStream.flush();
                        ouputStream.close();
                  }
             } catch (IOException e) {
                  logger.error(e);
             }
        }

	}
	//设置表头
    private void buildTitle() {
    	int rowIndex=0;
    	//表头样式
        CellStyle totalHeadStyle = new POIStyleWrapper( workbook)
                .setBackGround(IndexedColors.SEA_GREEN.getIndex())
                .setBoldFont( "宋体" , 16, IndexedColors.WHITE .getIndex())
                .setAlignment(CellStyle. ALIGN_CENTER)
                .getCellStyle();
    	//如果传有总表头则设置
    	if(totalTitle!=null&&!totalTitle.trim().equals("")){
    		 CellRangeAddress cra=new CellRangeAddress(0, 0, 0, 5);        
             sheet.addMergedRegion(cra);  
             Row totalTitleRow = sheet.createRow(rowIndex++); 
             new POICellWrapper(totalTitleRow,0).setStringValue(totalTitle).setCellStyle(totalHeadStyle);
    	}
    	CellStyle headStyle = new POIStyleWrapper( workbook)
        .setBackGround(IndexedColors.SEA_GREEN.getIndex())
        .setBoldFont( "宋体" , 12, IndexedColors.WHITE .getIndex())
        .setAlignment(CellStyle. ALIGN_CENTER)
        .getCellStyle();
      Row titleRow = sheet.createRow(rowIndex);               //表头行
      if(columns!=null){
    	  String[] columnStr = columns.split(",");
    	  for ( int i = 0; i < titles.length; i++) {
              sheet.setColumnWidth(i, Integer.parseInt(columnStr[i])*255);      //设置宽度
              new POICellWrapper(titleRow,i).setStringValue(titles[i]).setCellStyle(headStyle);
    	  }
      }else{
    	  for ( int i = 0; i < titles.length; i++) {
    		  new POICellWrapper(titleRow,i).setStringValue(titles[i]).setCellStyle(headStyle);
    	  }
      }
    }
    
    //设置内容
    private void buildContent() {
    	//内容样式
       CellStyle contentStyle = new POIStyleWrapper(workbook).setNormalFont("宋体", 12).setSolidBorder().getCellStyle();
       CellStyle contentNumFormatStyle = new POIStyleWrapper(workbook).setNormalFont("宋体", 12).setSolidBorder().setDataFormat("0.00").getCellStyle();

       //循环数据
       int rowIndex = 1;
       if(totalTitle!=null&&!totalTitle.trim().equals("")){
    	   rowIndex = 2;
       }
       BigDecimal temInt = null;
       BigDecimal temPrem = null;
       for(Map<String,Object> map:results){
    	   Row row = sheet.createRow(rowIndex);
    	   for ( int i = 0; i < titles.length; i++) {
    		   if("人数".equals(titles[i])){
    			   temInt = (BigDecimal) map.get("BBR_NUM");
    			   if(null==temInt){
    				   temInt = new BigDecimal(0);
    			   }
    			   new POICellWrapper(row,i).setDoubleValue(temInt.intValue()).setCellType(HSSFCell.CELL_TYPE_NUMERIC).setCellStyle(contentStyle);
    		   }else if("保费".equals(titles[i])||"含税保费".equals(titles[i])){
    			   temPrem = (BigDecimal) map.get("PREMIUM");
    			   if(null==temPrem){
    				   temPrem = new BigDecimal(0);
    			   }
    			   temPrem.setScale(2, BigDecimal.ROUND_HALF_UP);
    			   new POICellWrapper(row,i).setDoubleValue(temPrem.doubleValue()).setCellType(HSSFCell.CELL_TYPE_NUMERIC).setCellStyle(contentNumFormatStyle);
    		   }else if("车船税".equals(titles[i])){
    			   temPrem = (BigDecimal) map.get("VHL_TAX");
    			   if(null==temPrem){
    				   temPrem = new BigDecimal(0);
    			   }
    			   temPrem.setScale(2, BigDecimal.ROUND_HALF_UP);
    			   new POICellWrapper(row,i).setDoubleValue(temPrem.doubleValue()).setCellType(HSSFCell.CELL_TYPE_NUMERIC).setCellStyle(contentNumFormatStyle);
    		   }else{
    			   new POICellWrapper(row,i).setStringValue(map.get(titleKeys[i])!=null?map.get(titleKeys[i]).toString():"").setCellStyle(contentStyle);
    		   }
    	   }
    	   rowIndex ++;
       }
    }
} 
